package 进阶阶段;

import java_code.JDBCUtils;
import java.sql.*;

public class demo31JDBC {
    public static void main(String[] args) throws Exception{
        //Java Data Base Connectivity(Java数据库连接) JDBC是Java访问数据库的`标准规范`
        //JDBC是用于执行SQL语句的Java API(Java语言通过JDBC可以操作数据库)

        String database = "sys_config";//自定义一个数据库
        String data1 = "variable";//自定义一个数据库中的一个字段
        String data2 = "value";//自定义一个数据库中的一个字段

        System.out.println("直接读取数据库数据----------------------------------------------");

        //交给jdk的驱动管理类，注册驱动，创建驱动类对象
//        DriverManager.registerDriver(new com.mysql.jdbc.Driver());//拿到驱动客户端连接服务器  //mysql5.1.37版本
//        DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());//mysql8.0.30版本
        //Driver.class字节码对象(Class)加载到内存，然后津泰代码块走一次

//        Class driverClass = Driver.class;//解决了驱动注册两次的问题

        Class.forName("com.mysql.cj.jdbc.Driver");//Driver.class

        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys", "root", "");

        String sql = "select * from "+database+"";
        System.out.println("查询语句" + sql);

        Statement statement = connection.createStatement();//statement语句对象发送执行sql语句的功能
        ResultSet resultSet = statement.executeQuery("select * from "+database+"");
        System.out.println(resultSet);

        while (resultSet.next()){
            String id = resultSet.getString(data1);
            System.out.println(id);
        }

        resultSet.close();
        statement.close();
        connection.close();

        System.out.println("通过配置文件读取数据库数据------------------------------------------------");

        Connection connection1 = JDBCUtils.getConnection();

        Statement statement1 = connection1.createStatement();
        ResultSet resultSet1 = statement1.executeQuery("select * from "+database+"");

        while (resultSet1.next()){
            String id = resultSet1.getString(data1);
            System.out.println(id);
        }

        JDBCUtils.close(resultSet1, statement1, connection1);

        System.out.println("JDBC工具类增删改查--------------------------------------------------------");
        System.out.println("查询操作在通过配置文件读取数据库数据已经实现了");
        System.out.println("新增操作");
        Connection connection2 = JDBCUtils.getConnection();

        Statement statement2 = connection2.createStatement();
        int i2 = statement2.executeUpdate("insert into "+database+" values ('343' , 5 , '2022-08-13 17:29:56' , 2)");
        String sql2 = "insert into "+database+" values ('343' , 5 , '2022-08-13 17:29:56' , 2)";
        System.out.println("新增语句" + sql2);
        System.out.println("新增了" + i2 + "条数据");

        JDBCUtils.close(null , statement2 , connection2);



        System.out.println("修改操作");

        Connection connection3 = JDBCUtils.getConnection();

        Statement statement3 = connection3.createStatement();
        int i3 = statement3.executeUpdate("update "+database+" set "+data1+" = 'aaa' where value = '5';");
        String sql3 = "update "+database+" set "+data1+" = 'aaa' where value = '5'";
        System.out.println("修改语句" + sql3);
        System.out.println("修改了" + i3 + "条数据");

        JDBCUtils.close(null , statement3 , connection3);



        System.out.println("删除操作");

        Connection connection4 = JDBCUtils.getConnection();

        Statement statement4 = connection4.createStatement();
        int i4 = statement4.executeUpdate("delete from "+database+" where "+data2+" = '5'");
        String sql4 = "delete from "+database+" where "+data2+" = '5'";
        System.out.println("删除语句" + sql4);
        System.out.println("删除了" + i4 + "条数据");

        JDBCUtils.close(null , statement4 , connection4);

        System.out.println("PreparedStatement预编译--------------------------------------------------------");
        //PreparedStatement是Statement子接口，解决sql注入问题，提高效率
        Connection connection5 = JDBCUtils.getConnection();

        String sql1 = "select * from "+database+" where "+data2+" = ?";//搞一个占位符？，把结构编译固定
        PreparedStatement preparedStatement = connection5.prepareStatement(sql1);
        String a = "6";
        preparedStatement.setString(1 , a);
        System.out.println(sql1);
        ResultSet resultSet2 = preparedStatement.executeQuery();
        while (resultSet2.next()){
            String id = resultSet2.getString(data1);
            System.out.println(id);
        }

    }
}
